import json
import logging

from database.connection import Link_database
# from ydsp.database.connection import Link_database

logger = logging.getLogger(__name__)


def EntryEmployeesSQL(data):
    """员工录入"""
    con, obj = Link_database()  # 链接数据库

    try:
        name = data['name']
        gender = data['gender']
        born_date = data['born_date']
        height = data['height']
        weight = data['weight']
        ethnic = data['ethnic']
        political = data['political']
        marriage = data['marriage']
        id_card = data['id_card']
        phone = data['phone']
        emergency_phone = data['emergency_phone']
        hk_address = data['hk_address']
        jt_address = data['jt_address']
        xz_address = data['xz_address']
        learn = data['learn']
        professional = data['professional']
        graduation_date = data['graduation_date']
        school = data['school']
        members = data['members']
        jobs = data['jobs']
        department = data['department']
        induction_date = data['induction_date']
        bank_name = data['bank_name']
        bank = data['bank']
        # serial = data['serial']
        director_general = data['director_general']  # 总监姓名
        supervisor = data['supervisor']  # 主管姓名
    except Exception as e:
        logger.error(e)
        return False

    # print(department)
    # sql = """select id from depar where department='%s'""" % department
    # obj.execute(sql)
    # n = obj.fetchone()
    # if n:
    #     department_id = n[0]
    # else:
    #     return False

    sql_1 = """insert into user (name,gender,born_date,height,weight,ethnic,political,marriage,id_card,phone,emergency_phone,hk_address,jt_address,xz_address,learn,professional,graduation_date,school,members,jobs,department,induction_date,bank_name,bank) values 
            ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,'%s','%s','%s')""" % \
            (name, gender, born_date, height, weight, ethnic, political, marriage, id_card, phone, emergency_phone,
             hk_address, jt_address, xz_address, learn, professional, graduation_date, school, members, jobs, department,
             induction_date, bank_name, bank)
    obj.execute(sql_1)

    sql = """select id from user where name='%s'""" % director_general
    obj.execute(sql)
    der_id = 0  # 总监id
    d = obj.fetchone()
    if d:
        der_id = d[0]

    sql_2 = """select id from user where name='%s'""" % supervisor
    obj.execute(sql_2)
    sup_id = 0  # 主管id
    s = obj.fetchone()
    if s:
        sup_id = s[0]

    sql_3 = """select id from user order by id desc limit 1"""
    obj.execute(sql_3)
    user_id = obj.fetchone()[0]  # 用户id

    sql_4 = """insert into depar_user (department,director,tube,user_id) values (%d,%d,%d,%d)""" % (int(department), der_id, sup_id, user_id)
    obj.execute(sql_4)

    sql_6 = """select checked,sidenavdata from depar where id={}""".format(department)
    obj.execute(sql_6)
    checked, sideNavData = obj.fetchone()

    sql_5 = """insert into permission_table (user_id,checked,permission_data) values ({},'{}','{}')""".format(user_id, checked, sideNavData)
    obj.execute(sql_5)
    con.commit()

    obj.close()
    con.close()

    return True


def UpdateEntrySQL(data):
    """员工信息修改"""
    con, obj = Link_database()

    try:
        id = int(data['user_id'])
        name = data['name']
        gender = data['gender']
        born_date = data['born_date']
        height = data['height']
        weight = data['weight']
        ethnic = data['ethnic']
        political = data['political']
        marriage = data['marriage']
        id_card = data['id_card']
        phone = data['phone']
        emergency_phone = data['emergency_phone']
        hk_address = data['hk_address']
        jt_address = data['jt_address']
        xz_address = data['xz_address']
        learn = data['learn']
        professional = data['professional']
        graduation_date = data['graduation_date']
        school = data['school']
        members = data['members']
        jobs = data['jobs']
        # department = data['department']
        induction_date = data['induction_date']
        positive_date = data['positive_date']
        departure_date = data['departure_date']
        bank_name = data['bank_name']
        bank = data['bank']
        state = data['state']
        serial = data['serial']
    except Exception as e:
        logger.error(e)
        return False

    sql = """update user set name='%s',gender='%s',born_date='%s',height='%s',weight='%s',ethnic='%s',political='%s',marriage='%s',
              id_card='%s',phone='%s',emergency_phone='%s',hk_address='%s',jt_address='%s',xz_address='%s',learn='%s',professional='%s',
              graduation_date='%s',school='%s',members='%s',jobs='%s',induction_date='%s',positive_date='%s',departure_date='%s',
              bank_name='%s',bank='%s',state='%s',serial='%s' where id=%d""" % \
                (name, gender, born_date, height, weight, ethnic, political, marriage, id_card, phone, emergency_phone, hk_address,
                 jt_address, xz_address, learn, professional, graduation_date, school, members, jobs, induction_date, positive_date,
                 departure_date, bank_name, bank, state, serial, id)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()

    return True


class AssetAllocaSQL(object):
    """资产分配"""
    def __init__(self, serial, user_id):
        self.serial = serial
        self.user_id = user_id
        self.con, self.obj = Link_database()

    # def save(self, user_id):
    #     sql = """insert into assets (name,number,state,user_id) values ('%s',%d,'%s',%d)""" % (self.name, self.number, self.state, user_id)
    #     self.obj.execute(sql)
    #     self.con.commit()

    def sel_department(self):
        """查询部门"""
        sql = """select department from user where id={}""".format(self.user_id)
        self.obj.execute(sql)
        self.department = self.obj.fetchone()[0]

    def update(self):
        self.sel_department()
        sql = """update assets set department=%d,user_name=%d where id=%d""" % (self.department, self.user_id, self.serial)
        self.obj.execute(sql)
        self.con.commit()

    def __del__(self):
        self.obj.close()
        self.con.close()


# def UpdateAAssetSQL(id, name, number, state):
#     """修改个人资产"""
#     con, obj = Link_database()
#
#     sql = """update assets set name='%s',number=%d, """


def AllAssetAllocaSQL(name, number):
    """总资产生成"""
    con, obj = Link_database()

    for i in range(number):
        sql = """insert into assets (assets_name) values ('%s')""" % name
        obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def UpdateAAssetSQL(serial, state, note):
    """个人资产修改"""
    con, obj = Link_database()

    sql = """update assets set state='%s',note='%s' where id=%d""" % (state, note, serial)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def RetAssetSQL(serial):
    con, obj = Link_database()

    sql = """update assets set department=0,user_name=0 where id=%d""" % serial
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


if __name__ == '__main__':
    data = {"name":"1","gender":"1","born_date":"1","height":"1","weight":"1","ethnic":"1","political":"1","marriage":"1","id_card":"1","phone":"1","emergency_phone":"1","hk_address":"1","jt_address":"1","xz_address":"1","learn":"1","professional":"1","graduation_date":"1","school":"1","members":"1","jobs":"1","induction_date":"1","bank_name":"1","bank":"1","department":"软件部","serial":"1"}
    print(EntryEmployeesSQL(data))
